Modern commercial database design includes a host of complex data considerations involving how to store, manage, and manipulate large amounts of data. Such data often includes intricate relationships to other data such as in an object tree providing inheritance properties between various objects. These types of relationships often complicate the efficient design of databases and components to manage such data. For example, one aspect to the database design process lies in understanding the way a relational database management system stores data. To efficiently and accurately provide users with information, a database program needs to access facts (data) about different subjects stored in separate tables. For example, one table may only store facts about employees, and another table may only store facts regarding sales, and then other tables for some other corporate matter. When using data, these facts are then automatically combined and presented in many different ways. For example, users may print reports that combine facts about employees and facts about sales.
Generally, to design a database, information is broken down in some order such as separate subjects in a library and then a database program determines how the subjects are related. These programs often include a relational database query using a common database language such as Structured Query Language (SQL). Before such languages can be applied to data, several decisions are usually made as to which types of data are important and how such data should be organized. For instance, these decisions may include determining the scope of a database to decide which data to store therein. Then determining the tables needed to divide information into separate subjects, such as “Employees” or “Orders.” Each subject will then be a table in the database. Other aspects include determining respective fields that are needed in order to decide what information to store in each table. Each category of information in a table is called a field and is displayed as a column in the table. For example, one field in an Employees table could be Last Name; another could be Hire Date. Another consideration is to determine relationships such as deciding how data in one table is related to the data in other tables. Designers often add fields to tables or create new tables to clarify the relationships, as necessary.
There are several common pitfalls that may be encountered when designing a database. These problems can cause data to be harder to use and maintain. These may include having one table with a large number of fields that don't all relate to the same subject. For example, one table might contain fields pertaining to customers as well as fields that contain sales information. Also, it is often more efficient if each table contains data regarding only one subject. In other cases, overhead is created when fields are intentionally left blank in many records because they are not applicable to those records. This usually implies that the fields belong in another table. Redundancy is another problem when a large number of tables, many of which having the same fields. For example, when separating tables for January sales and February sales, or for local customers and remote customers, in which there is a redundant store of the same type of information. Thus, one technique is consolidating all the information pertaining to a single subject in one table.
In addition to the complexities of how to setup and design the tables and fields of the database, other considerations must be taken. These include how should data security be provided for the respective tables and fields (e.g., security such as who or what can access a file). This includes how to provide security to complex structures stored in databases such as hierarchical objects. Classically, security considerations have been propagated in an inheritance hierarchy for such objects, wherein each item in the hierarchy would need to be updated if one of the items were changed. However, there is a common problem faced by any implementation that utilizes relational database's table rows to store hierarchical objects, which is how to set security information or data on each object and populate the security data to its child objects based on the inheritance model.